/* Program name: auto_debt.sas*/
/* Objective: create an estimate of vehicle-related debt and student loan debt.*/
/* This code requires the clo variable, which is created in clean_tradeline99.sas, so cannot 
    be run on the raw tradeline files*/
/* Note: HARDCODED DATES BELOW*/

libname in '/data';
libname scorein '/data';
%include "/data/tu_formats.sas";
%include "/data/06macros.sas";


data temp (keep = int teditseq terms hicredit acctype loantypt loantype acctbal ecoa crdtlim cll auto_: mortgage_: dtveri student_: ccard_: cctot_: install_: lcred_: unsec_: secgoods_: lease_: dtclose dtopen paypat mop rename=(dtclose=dtclo));
  set in.cleantrade99;

  if clo eq 1 or dispcd in ('IA','INA','FTS','FTB','ETS','ETB','ETI') then coo = 1;
  else coo = 0;

  if dtclose gt 0 or coo eq 1 then cll = 1;
  else if acctype in ('I', 'M') and acctbal le 0 and mop eq 1 then cll = 1;
  else cll = 0;

  if acctbal lt 0 then acctbal = .;

/************************ADDED CODE 5/2019*****************/


/* installment loans */
  if loantypt in('IS') then do;
     loantype = "INSTALL"; 
     install_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     install_hist_ontime = paymop1_24;
     install_hist_3060 = pay3059_24;
     install_hist_6090 = pay6089_24;
     install_hist_90120 = pay90119_24;
     install_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then install_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 199807 then do;
	      if acctbal eq 0 then install_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(install);
	end;
     end;
  end;

/* line of credit */
  if loantypt in('LC') then do;
     loantype = "LCRED"; 
     lcred_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lcred_hist_ontime = paymop1_24;
     lcred_hist_3060 = pay3059_24;
     lcred_hist_6090 = pay6089_24;
     lcred_hist_90120 = pay90119_24;
     lcred_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lcred_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 199807 then do;
	      if acctbal eq 0 then lcred_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lcred);
	end;
     end;
  end;


/* Unsecured */
  if loantypt in('US') then do;
     loantype = "UNSEC"; 
     unsec_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     unsec_hist_ontime = paymop1_24;
     unsec_hist_3060 = pay3059_24;
     unsec_hist_6090 = pay6089_24;
     unsec_hist_90120 = pay90119_24;
     unsec_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then unsec_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 199807 then do;
	      if acctbal eq 0 then unsec_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(unsec);
	end;
     end;
  end;

/* Household goods secured & collateral */
  if loantypt in('SO','SH') then do;
     loantype = "SECGOODS"; 
     secgoods_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     secgoods_hist_ontime = paymop1_24;
     secgoods_hist_3060 = pay3059_24;
     secgoods_hist_6090 = pay6089_24;
     secgoods_hist_90120 = pay90119_24;
     secgoods_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then secgoods_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 199807 then do;
	      if acctbal eq 0 then secgoods_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(secgoods);
	end;
     end;
  end;


/* lease */
  if loantypt in('LE') then do;
     loantype = "LEASE"; 
     lease_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lease_hist_ontime = paymop1_24;
     lease_hist_3060 = pay3059_24;
     lease_hist_6090 = pay6089_24;
     lease_hist_90120 = pay90119_24;
     lease_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lease_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 199807 then do;
	      if acctbal eq 0 then lease_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lease);
	end;
     end;
  end;


/************************STOP ADDED CODE 5/2019*****************/

  /* total auto_loans*/
  if loantypt in('AU','AL','AR','AT','CA','MB','MT','RV') then do;
     loantype = "AUTO"; 
     auto_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     auto_hist_ontime = paymop1_24;
     auto_hist_3060 = pay3059_24;
     auto_hist_6090 = pay6089_24;
     auto_hist_90120 = pay90119_24;
     auto_hist_120plus	= pay120up_24;

     /*how many loans have been closed */
     if (cll eq 1 or acctbal le 0) then auto_clo = 1;
	
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 then do;
        if acctbal eq 0 then auto_clo=1;
        if acctbal gt 0 then do;
	   %recentcount(auto);
	end;
     end;
  end;

    /* total student debt*/
  if loantypt ='ST' then do;
     loantype = "STUD";
     student_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     student_hist_ontime = paymop1_24;
     student_hist_3060 = pay3059_24;
     student_hist_6090 = pay6089_24;
     student_hist_90120 = pay90119_24;
     student_hist_120plus = pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then student_clo = 1;
	

     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 then do;
        if acctbal eq 0 then student_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(student);
     	end;
     end;
  end;

  /* total mortgage debt*/
  if loantypt in ('CV', 'FH', 'FI', 'FR', 'HE', 'HI', 'PI', 'RE', 'RL', 'RM', 'RT', 'SM', 'VA', 'VM','UK',' ') and acctype eq 'M' then do;
     loantype = "MORT"; 
     mortgage_open = 1;

     mortgage_hist_ontime = paymop1_24;
     mortgage_hist_3060 = pay3059_24;
     mortgage_hist_6090 = pay6089_24;
     mortgage_hist_90120 = pay90119_24;
     mortgage_hist_120plus = pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then mortgage_clo = 1;
	
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 then do;
        if acctbal eq 0 then mortgage_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(mortgage);
     	end;
     end;
  end;


  /* revolving debt*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
    people have no credit cards*/

    if loantypt in ('CC') then do;  
     loantype = "CCAR";
     
     if ecoa = "I" then do;
     	ccard_indiv_open = 1;
     	ccard_indiv_hist_ontime = paymop1_24;
	    ccard_indiv_hist_3060 = pay3059_24;
     	ccard_indiv_hist_6090 = pay6089_24;
     	ccard_indiv_hist_90120 = pay90119_24;
     	ccard_indiv_hist_120plus = pay120up_24;
     end;

      if ecoa = "C" then do;
     	ccard_joint_open = 1;
			ccard_joint_hist_ontime = paymop1_24;
			ccard_joint_hist_3060 = pay3059_24;
     	ccard_joint_hist_6090 = pay6089_24;
     	ccard_joint_hist_90120 = pay90119_24;
     	ccard_joint_hist_120plus = pay120up_24;
     end;

     if ecoa in ("M", "S", "C") then do;
     ccard_cosign_open = 1;
		 ccard_cosign_hist_ontime = paymop1_24;
	   ccard_cosign_hist_3060 = pay3059_24;
     	ccard_cosign_hist_6090 = pay6089_24;
     	ccard_cosign_hist_90120 = pay90119_24;
     	ccard_cosign_hist_120plus = pay120up_24;
     end;

     if ecoa = "A" then do;
     ccard_notliab_open = 1;
	   ccard_notliab_hist_ontime = paymop1_24;
	   ccard_notliab_hist_3060 = pay3059_24;
     	ccard_notliab_hist_6090 = pay6089_24;
     	ccard_notliab_hist_90120 = pay90119_24;
     	ccard_notliab_hist_120plus = pay120up_24;
     end;

     if ecoa in ("A", "P") then do;
      ccard_maybeliab_open = 1;
	    ccard_maybeliab_hist_ontime = paymop1_24;
	    ccard_maybeliab_hist_3060 = pay3059_24;
     	ccard_maybeliab_hist_6090 = pay6089_24;
     	ccard_maybeliab_hist_90120 = pay90119_24;
     	ccard_maybeliab_hist_120plus = pay120up_24;
     end;

     * individual account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then ccard_indiv_clo = 1;
   
     /*open account balance, delinquency info*/
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 and ecoa = "I" then do;
     	%recentcount(ccard_indiv);
     end;
 
     * joint account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then ccard_joint_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 and ecoa = "C" then do;
	%recentcount(ccard_joint);
     end;

     * cosigner;     
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then ccard_cosign_clo = 1;
	   
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 and ecoa in ("M", "S", "C") then do;
        %recentcount(ccard_cosign);
     end; 

     * not liable;     
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then ccard_notliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 and ecoa in ("A") then do;
        %recentcount(ccard_notliab);
     end; 

     * maybe liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A", "P") then ccard_maybeliab_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 and ecoa in ("A", "P") then do;
        %recentcount(ccard_maybeliab);
     end;
  end;

  /* credit card total debt (secured + unsecured)*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
  people have no credit cards*/
  if loantypt in('CC','SC') then do;

     if ecoa = "I" then do;
     cctot_indiv_open = 1;
     cctot_indiv_hist_ontime = paymop1_24;
	   cctot_indiv_hist_3060 = pay3059_24;
     cctot_indiv_hist_6090 = pay6089_24;
     cctot_indiv_hist_90120 = pay90119_24;
     cctot_indiv_hist_120plus = pay120up_24;
     end;

     if ecoa = "C" then do;
      cctot_joint_open = 1;
	    cctot_joint_hist_ontime = paymop1_24;
	    cctot_joint_hist_3060 = pay3059_24;
     	cctot_joint_hist_6090 = pay6089_24;
     	cctot_joint_hist_90120 = pay90119_24;
     	cctot_joint_hist_120plus = pay120up_24;
     end;

     if ecoa in ("M", "S", "C") then do;
     	cctot_cosign_open = 1;
	    cctot_cosign_hist_ontime = paymop1_24;
	    cctot_cosign_hist_3060 = pay3059_24;
     	cctot_cosign_hist_6090 = pay6089_24;
     	cctot_cosign_hist_90120 = pay90119_24;
     	cctot_cosign_hist_120plus = pay120up_24;
     end;

     if ecoa = "A" then do;
     	cctot_notliab_open = 1;
	    cctot_notliab_hist_ontime = paymop1_24;
	    cctot_notliab_hist_3060 = pay3059_24;
     	cctot_notliab_hist_6090 = pay6089_24;
     	cctot_notliab_hist_90120 = pay90119_24;
     	cctot_notliab_hist_120plus = pay120up_24;
     end;

     if ecoa in ("A", "P") then do;
     	cctot_maybeliab_open = 1;
	    cctot_maybeliab_hist_ontime = paymop1_24;
	    cctot_maybeliab_hist_3060 = pay3059_24;
     	cctot_maybeliab_hist_6090 = pay6089_24;
     	cctot_maybeliab_hist_90120 = pay90119_24;
     	cctot_maybeliab_hist_120plus = pay120up_24;
     end;

     * individual account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then cctot_indiv_clo = 1;
	
     /*open account balance, delinquency info*/
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 and ecoa = "I" then do;
     	%recentcount(cctot_indiv);
     end;
 
     * joint account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then cctot_joint_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 and ecoa = "C" then do;
	%recentcount(cctot_joint);
     end;

     * cosigner;     
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then cctot_cosign_clo = 1;
	     
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 and ecoa in ("M", "S", "C") then do;
        %recentcount(cctot_cosign);
     end; 

     * not liable;     
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then  cctot_notliab_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 and ecoa in ("A") then do;
        %recentcount(cctot_notliab);
     end; 

     * maybe liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A", "P") then cctot_maybeliab_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 199807 and ecoa in ("A", "P") then do;
        %recentcount(cctot_maybeliab);
     end;
  end;
run;

proc sort data = temp;
  by teditseq;
run;
/*select variables that we want to get sum for collapsed dataset*/
proc sql;
     select name
     into :sumlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_debt' or name ? '_num' or name like '%_ontime' 
     or name like '_lim' or name like '%3060' or name like '%6090' or name like '%90120' 
     or name like '%120plus' or name like '%_worse'
     or name ? '_clo' or name ? '_open');
     quit;
/*select lot variables that we want to get min (choose earliest date)*/
proc sql;
     select name 
     into :minlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_lot' or name ? '_f120');
     quit;
/*collapse the dataset*/
 proc means data = temp noprint;
  by teditseq;
  var install: lcred: unsec: secgoods: lease: auto: trans: student: mortgage: ccard: cctot:;
  output out=rev_install_type99 min(&minlist)= sum(&sumlist)=;
run;

data rev_install_type99;
    set rev_install_type99;
    drop _type_ _freq_;

    ccard_indiv_joint_debt=sum(ccard_indiv_debt,.5*ccard_cosign_debt);
    cctot_indiv_joint_debt=sum(cctot_indiv_debt,.5*cctot_cosign_debt);

ccard_indiv_joint_lim=sum(ccard_indiv_lim,.5*ccard_cosign_lim);
    cctot_indiv_joint_lim=sum(cctot_indiv_lim,.5*cctot_cosign_lim);

run;


proc sort data=rev_install_type99; by teditseq; run;
proc sort data=temp; by teditseq; run;


data temp_new;

terms hicredit install_clo install_open lcred_clo lcred_open unsec_clo unsec_open secgoods_clo secgoods_open lease_clo lease_open merge temp (in = a keep= teditseq dtopen dtveri dtclo  student_clo student_open mortgage_clo mortgage_open auto_clo auto_open loantype student_debt int crdtlim acctbal ecoa
                    rename=(student_debt=student_debt_tl student_clo=student_clo_tl student_open=student_open_tl 
install_clo=install_clo_tl 
install_open = install_open_tl
lcred_clo = lcred_clo_tl
lcred_open = lcred_open_tl 
unsec_clo = unsec_clo_tl 
unsec_open = unsec_open_tl 
secgoods_clo = secgoods_clo_tl
secgoods_open = secgoods_open_tl
lease_clo = lease_clo_tl
lease_open = lease_open_tl 
terms=terms_tl 
hicredit=hicredit_tl 
mortgage_clo=mortgage_clo_tl 
mortgage_open=mortgage_open_tl auto_clo=auto_clo_tl auto_open=auto_open_tl crdtlim=crdtlim_tl acctbal=acctbal_tl ecoa=ecoa_tl))




 rev_install_type99 (in = b);
       by teditseq;      
if a; 
run;




/* pull roll up & locational variables from SCORE dataset */
/* we rename the variables to chVARNAME in order to convert them from character to numeric*/
/* we do this because the variables in 03-08 are numeric, and we want them all formatted the same*/
%let renamelist=CBSA=chCBSA COUNTY=chCOUNTY LAT=chLAT LONG=chLONG STATE=chSTATE TRACT=chTRACT ZIP=ZIP99;

data roll_up99(drop=AT01 ch:);
     set scorein.score99 (keep= teditseq RE28 RE34 AT33 AT28 ON33 MT33 IN33 RE33 G041-G045 G057-G071 G082-G087 CBSA 
     	 		 	COUNTY LAT LONG STATE TRACT ZIP score AT01 
     	 		 	G104 G093-G094 G096 S059 S063-S064
			  rename=(&renamelist));
     rename score=tr_am;
     /*if AT01 ne 0;	 we want to include obs with 0 tradelines from score per conversation
     	      	 	 with alvaro - AN 06.20.13 */
     CBSA=input(chCBSA,8.);
     COUNTY=input(chCOUNTY,8.);
     LAT=input(chLAT,8.);
     LONG=input(chLONG,8.);
     STATE=input(chSTATE,8.);
     TRACT=input(substr(chTRACT,1,4),8.);
     SUFFIX=input(substr(chTRACT,5,2),8.);     
run;

data in.roll_trades99(rename=(teditseq=teditseq99));
     merge roll_up99 
           temp_new ;
     by teditseq;  /* the merge should be uncondtional on in=a or in=b*/  
run;


proc contents data=in.roll_trades99;
run;




